Data Analytics
Data Frame Pivot Table vLookup Simple Ratios
if __name__ == "__main__":
revenue = 100000
net_profit = 20000
total_assets = 150000
total_equity = 80000
current_assets = 50000
current_liabilities = 25000
total_debt = 70000
# 1. Profitability Ratios
net_profit_margin = net_profit / revenue
roa = net_profit / total_assets
roe = net_profit / total_equity
# 2. Liquidity Ratio
current_ratio = current_assets / current_liabilities
# 3. Leverage Ratio
debt_to_equity = total_debt / total_equity
# Print results
print("Financial Ratios:\n")
print(f"Net Profit Margin: {net_profit_margin:.2f}")
print(f"Return on Assets (ROA): {roa:.2f}")
print(f"Return on Equity (ROE): {roe:.2f}")
print(f"\nCurrent Ratio: {current_ratio:.2f}")
print(f"Debt to Equity Ratio: {debt_to_equity:.2f}")
import pandas as pd
if __name__=="__main__":
records = {
"names":["Anita","Dina","Geena","Niroj"],
"income":[2000,2300,3000,2500],
"expenses":[1000,1200,1900,1700]
}
df = pd.DataFrame(records)
print(df)
print(f"Sum: {df["income"].sum()}")
print(f"Mean: {df['income'].mean()}")
print(f"Median: {df['income'].median()}")
print(f"Max: {df['income'].max()}")
print(f"Min: {df['income'].min()}")
print(f"Stdev: {df['income'].std()}")
print(f"Variance: {df['income'].var()}")
print(f"Count: {df['income'].count()}")
print(f"Differnce:{df['income'].max()-df['income'].min()}")
print(f"Difference between std and range: {df['income'].std() - (df['income'].max() - df['income'].min())}")
import pandas as pd
#Excel's VLOOKUP, the equivalent operation in pandas is usually done with merge(), map(), or join().
#Main DataFrame (like where you want VLOOKUP results)
df_sales = pd.DataFrame({
'Product_ID': [101, 102, 103, 104,105],
'Quantity': [5, 8, 3, 10,6]
})
#print(df_sales)
df_products = pd.DataFrame({
'Product_ID': [101, 102, 103, 104],
'Product_Name': ['Apple', 'Banana', 'Orange', 'Mango'],
'Price': [100, 80, 120, 150]
})
#print(df_products)
result = pd.merge(
df_sales,
df_products[['Product_ID', 'Price','Product_Name']],
on='Product_ID',
#how='left' if how is specified it will be like left join in SQL
)
print("The pivot table: ")
print(result)
#Using map() (Fast for One Column)
print("Using MAP")
#Now Product_ID is no longer a regular column; it becomes the DataFrame index.
price_dict = df_products.set_index('Product_ID')['Price']
#following statement creates a Series, where Index = Product_ID, values = Price
df_sales['Price'] = df_sales['Product_ID'].map(price_dict)
print(df_sales)
#Multiple Columns Lookup
print("Multiple Columns Lookup")
result = pd.merge(
df_sales,
df_products,
on='Product_ID',
how='left'
)
print(result)
#In pandas, the equivalent of an Excel Pivot Table is pivot_table().
"""
df.pivot_table(
values='column_to_aggregate',
index='row_group',
columns='column_group',
aggfunc='mean' # sum, count, max, min, etc.
)
"""
df = pd.DataFrame({
'Company': ['NABIL', 'NABIL', 'NICA', 'NICA'],
'Day': ['Monday', 'Tuesday', 'Monday', 'Tuesday'],
'Close': [1000, 2010, 500, 510]
})
pivot = df.pivot_table(
values='Close',
index='Company',
columns='Day'
)
print(pivot)
#Average Closing Price by Weekday
print("Average Closing Price by Weekday")
pivot = df.pivot_table(
values='Close',
index='Company',
#columns='Day',
aggfunc='mean'
)
print(pivot)
#Count Number of Trades
print("Count Number of Trades")
pivot = df.pivot_table(
values='Close',
index='Company',
#columns='Day',
aggfunc='count'
)
print(pivot)
print("Sum of Trades")
pivot = df.pivot_table(
values='Close',
index='Company',
columns='Day',
aggfunc='sum'
)
print(pivot)
#Multiple Aggregations
print("Multiple Aggregations")
pivot = df.pivot_table(
values='Close',
index='Company',
columns='Day',
aggfunc=['mean', 'max', 'min']
)
print(pivot)
#Add Totals (Like Excel Grand Total)
print("Add Totals, Like Excel Grand Total")
pivot = df.pivot_table(
values='Close',
index='Company',
columns='Day',
aggfunc='mean',
margins=True
)
print(pivot)